package cn.com.chec.en.dao;

import java.io.File;
import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Collections;
import java.util.Comparator;
import java.util.Date;
import java.util.List;

import org.apache.commons.lang.time.DateFormatUtils;
import org.apache.log4j.Logger;
import org.springframework.stereotype.Repository;

import cn.com.chec.en.model.domain.TableDesc;
import cn.com.chec.en.util.FileUtils;
import cn.com.chec.en.util.PropertiesUtil;
import cn.com.chec.en.util.StringTools;

@Repository
public class DBInfoDao {
	protected static final Logger logger = Logger.getLogger(DBInfoDao.class);

	public List<String> getAllTables() {
		List<String> list = new ArrayList<String>();
		try {
			Connection conn = DBFactory.getInstance().getConnection();
			String sql = "show tables";
			PreparedStatement pstmt = conn.prepareStatement(sql);
			ResultSet rs = pstmt.executeQuery();
			while (rs.next()) {
				list.add(rs.getString(
						"Tables_in_" + PropertiesUtil.getValue("mysqlDBName"))
						.toLowerCase());
			}
			rs.close();
			pstmt.close();
			conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return list;
	}

	public synchronized boolean dataDump(String dbBasePath, String dbName,
			String tblNames, String destSQLDir) {
		boolean result = false;
		dbBasePath = StringTools.decoder(dbBasePath).replaceAll("/", "\\\\");
		destSQLDir = StringTools.decoder(destSQLDir);
		FileUtils.mkdirs(destSQLDir);
		logger.info("[data dump] " + dbName + " [" + tblNames + "] to " + destSQLDir);
		try {
			String sqlName = dbName + "_"
					+ DateFormatUtils.format(new Date(), "yyyyMMddHHmmss")
					+ ".sql";
			String dumpCommond = "cmd /c \"" + dbBasePath
					+ "mysqldump.exe\" -h127.0.0.1 -u"
					+ PropertiesUtil.getValue("mysqlDBUsername") + " -p"
					+ PropertiesUtil.getValue("mysqlDBPassword") + " " + dbName
					+ " " + tblNames + " > " + destSQLDir + sqlName;
			logger.info("[data dump] " + dumpCommond);

			Runtime.getRuntime().exec(dumpCommond);

			result = true;
		} catch (IOException e) {
			logger.error("[data dump] eroor ! " + dbName + " [" + tblNames
					+ "] to " + destSQLDir, e);
		}
		return result;
	}

	public synchronized boolean dataImport(String dbBasePath, String dbName,
			String sqlFile) {
		boolean result = false;
		dbBasePath = StringTools.decoder(dbBasePath).replaceAll("/", "\\\\");
		sqlFile = StringTools.decoder(sqlFile).replaceAll("/", "\\\\");

		logger.info("[data import] " + dbName + " use " + sqlFile);
		try {
			String importCommond = "cmd /c \"" + dbBasePath
					+ "mysql\" -h127.0.0.1 -u"
					+ PropertiesUtil.getValue("mysqlDBUsername") + " -p"
					+ PropertiesUtil.getValue("mysqlDBPassword") + " " + dbName
					+ " < " + sqlFile;

			logger.info("[data import] " + importCommond);

			Runtime.getRuntime().exec(importCommond);

			result = true;
		} catch (IOException e) {
			logger.info("[data import] error !  " + dbName + " use " + sqlFile);
		}
		return result;
	}

	public List<File> getSubDumpList(String dumpDir, int start, int num) {
		File[] files = new File(dumpDir).listFiles();
		List<File> list = Arrays.asList(files);
		Collections.sort(list, new Comparator<File>() {
			@Override
			public int compare(File o1, File o2) {
				return o1.lastModified() < o2.lastModified() ? 1 : 0 ;
			}
		});
		int len = (list != null) && (list.size() > 0) ? list.size() : 0;
		if (len <= num) {
			return list;
		}

		if (len > start + num) {
			return list.subList(start, start + num);
		}

		return list.subList(start, len);
	}

	public int getCountDumpList(String dumpDir) {
		File[] files = new File(dumpDir).listFiles();
		return files.length;
	}

	public List<TableDesc> getTableDesc(String tblName) {
		List<TableDesc> list = new ArrayList<TableDesc>();
		Connection conn = DBFactory.getInstance().getConnection();
		String descshowTable = "desc " + tblName;
		try {
			Statement stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery(descshowTable);
			TableDesc desc = null;
			while (rs.next()) {
				desc = new TableDesc();
				desc.setField(rs.getString("Field"));
				desc.setType(rs.getString("Type"));
				desc.setIsNull(rs.getString("Null"));
				desc.setKey(rs.getString("Key"));
				desc.setIsDefault(rs.getString("Default"));
				desc.setExtra(rs.getString("Extra"));
				list.add(desc);
			}
			rs.close();
			stmt.close();
			conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return list;
	}

	public static void main(String[] args) {
		DBInfoDao dao = new DBInfoDao();

		dao.dataImport(PropertiesUtil.getValue("mysqlBasePath") + "bin/",
				PropertiesUtil.getValue("mysqlDBName"), "c:/user.sql");
	}
}